import { sqliteTable, text, integer, real, index } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

export const products = sqliteTable('products', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  description: text('description'),
  price: real('price').notNull(),
  sku: text('sku').unique(),
  imageUrl: text('image_url'),
  category: text('category'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
}, (table) => ({
  nameIdx: index('product_name_idx').on(table.name),
  categoryIdx: index('product_category_idx').on(table.category),
}));

export const inventory = sqliteTable('inventory', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  productId: integer('product_id').references(() => products.id).notNull(),
  quantity: integer('quantity').notNull().default(0),
  lowStockThreshold: integer('low_stock_threshold').default(10),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
}, (table) => ({
  productIdx: index('inventory_product_idx').on(table.productId),
}));

export const customers = sqliteTable('customers', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').unique(),
  phone: text('phone'),
  phone2: text('phone2'),
  phone2Purpose: text('phone2_purpose'),
  wechat: text('wechat'),
  wechatPurpose: text('wechat_purpose'),
  qq: text('qq'),
  qqPurpose: text('qq_purpose'),
  whatsapp: text('whatsapp'),
  whatsappPurpose: text('whatsapp_purpose'),
  telegram: text('telegram'),
  telegramPurpose: text('telegram_purpose'),
  address: text('address'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
});

export const orders = sqliteTable('orders', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  customerId: integer('customer_id').references(() => customers.id),
  totalAmount: real('total_amount').notNull(),
  status: text('status', { enum: ['pending', 'paid', 'shipped', 'delivered', 'cancelled'] }).default('pending'),
  cancelReason: text('cancel_reason'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
}, (table) => ({
  customerIdx: index('order_customer_idx').on(table.customerId),
  statusIdx: index('order_status_idx').on(table.status),
}));

export const orderItems = sqliteTable('order_items', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  orderId: integer('order_id').references(() => orders.id).notNull(),
  productId: integer('product_id').references(() => products.id).notNull(),
  quantity: integer('quantity').notNull(),
  price: real('price').notNull(),
  subtotal: real('subtotal').notNull(),
});

export const orderActivities = sqliteTable('order_activities', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  orderId: integer('order_id').references(() => orders.id).notNull(),
  action: text('action', { enum: ['status_change', 'note_added', 'updated'] }).notNull(),
  fromStatus: text('from_status', { enum: ['pending', 'paid', 'shipped', 'delivered', 'cancelled'] }),
  toStatus: text('to_status', { enum: ['pending', 'paid', 'shipped', 'delivered', 'cancelled'] }),
  description: text('description'),
  performedBy: text('performed_by').default('system'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
}, (table) => ({
  orderIdx: index('order_activities_order_idx').on(table.orderId),
  actionIdx: index('order_activities_action_idx').on(table.action),
  dateIdx: index('order_activities_date_idx').on(table.createdAt),
}));

export const inventoryHistory = sqliteTable('inventory_history', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  productId: integer('product_id').references(() => products.id).notNull(),
  type: text('type', { enum: ['in', 'out', 'adjust', 'initial'] }).notNull(),
  quantity: integer('quantity').notNull(),
  previousQuantity: integer('previous_quantity'),
  newQuantity: integer('new_quantity'),
  notes: text('notes'),
  createdBy: text('created_by').default('system'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
}, (table) => ({
  productIdx: index('inventory_history_product_idx').on(table.productId),
  typeIdx: index('inventory_history_type_idx').on(table.type),
  dateIdx: index('inventory_history_date_idx').on(table.createdAt),
}));

export const inventoryCheck = sqliteTable('inventory_check', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  checkNumber: text('check_number').notNull(),
  checkDate: integer('check_date', { mode: 'timestamp' }).notNull(),
  status: text('status', { enum: ['draft', 'in_progress', 'completed', 'cancelled'] }).default('draft'),
  totalItems: integer('total_items').default(0),
  checkedItems: integer('checked_items').default(0),
  discrepancies: integer('discrepancies').default(0),
  notes: text('notes'),
  createdBy: text('created_by').default('system'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
  completedAt: integer('completed_at', { mode: 'timestamp' }),
}, (table) => ({
  checkNumberIdx: index('inventory_check_number_idx').on(table.checkNumber),
  statusIdx: index('inventory_check_status_idx').on(table.status),
  dateIdx: index('inventory_check_date_idx').on(table.checkDate),
}));

export const inventoryCheckItems = sqliteTable('inventory_check_items', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  checkId: integer('check_id').references(() => inventoryCheck.id).notNull(),
  productId: integer('product_id').references(() => products.id).notNull(),
  systemQuantity: integer('system_quantity').notNull(),
  actualQuantity: integer('actual_quantity').notNull(),
  discrepancy: integer('discrepancy').notNull(),
  notes: text('notes'),
  checkedBy: text('checked_by'),
  checkedAt: integer('checked_at', { mode: 'timestamp' }),
}, (table) => ({
  checkIdIdx: index('inventory_check_items_check_idx').on(table.checkId),
  productIdx: index('inventory_check_items_product_idx').on(table.productId),
}));

export const customerNotes = sqliteTable('customer_notes', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  customerId: integer('customer_id').references(() => customers.id).notNull(),
  content: text('content').notNull(),
  type: text('type', { enum: ['general', 'service', 'complaint', 'praise', 'followup'] }).default('general'),
  createdBy: text('created_by').default('admin'),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(sql`(strftime('%s', 'now'))`),
}, (table) => ({
  customerIdx: index('customer_notes_customer_idx').on(table.customerId),
  typeIdx: index('customer_notes_type_idx').on(table.type),
  dateIdx: index('customer_notes_date_idx').on(table.createdAt),
}));

